﻿using CDMS.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CDMS.Entities;

namespace CDMS.DAL
{
    public class Category_DAL
    {
        DBAccess db;
        EnumCategory enumCategory;
        string tableName;
        public Category_DAL(EnumCategory enumCategory)
        {
            db = new DBAccess();
            this.enumCategory = enumCategory;
            this.tableName = enumCategory.ToString();
        }

        public DataTable GetALL()
        {
            DataTable retVal = null;
            string sql = string.Format("SELECT * FROM {0}", tableName);
            retVal = db.GetDataTable(sql);
            return retVal;
        }

        public DataTable GetALLActivated()
        {
            DataTable retVal = null;
            string sql = string.Format("SELECT * FROM {0} WHERE {0}_Activated = 1 ", tableName);
            retVal = db.GetDataTable(sql);
            return retVal;
        }


        public Category GetOne(int Category_ID)
        {

            Category retVal = null;
            DataTable dt = new DataTable();

            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter(tableName+"_ID",Category_ID)
            };
            string sql = string.Format("SELECT * FROM {0} WHERE {0}_ID = @{0}_ID", tableName);

            dt = db.GetDataTable(sql, paramArray);
            if (dt != null)
                if (dt.Rows.Count > 0)
                {
                    retVal = new Category();
                    retVal.Category_Name = dt.Rows[0][tableName + "_Name"].ToString();
                    retVal.Category_Note = dt.Rows[0][tableName + "_Note"].ToString();
                    retVal.Category_Activated = Convert.ToBoolean(dt.Rows[0][tableName + "_Activated"]);
                    retVal.Category_ID = Convert.ToInt32(dt.Rows[0][tableName + "_ID"]);
                }
            return retVal;
        }

        public int Insert(Category entity)
        {
            int retVal;
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter(tableName+"_Name",entity.Category_Name),
                new SqlParameter(tableName+"_Note",entity.Category_Note)
            };
            string sql = string.Format("INSERT INTO {0} ({0}_Name,{0}_Note) VALUES(@{0}_Name,@{0}_Note) SELECT @@IDENTITY", tableName);
            retVal = Convert.ToInt32(db.ExecuteScalar(sql, paramArray));

            return retVal;
        }
        public bool Update(Category entity)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter(tableName+"_ID",entity.Category_ID),
                new SqlParameter(tableName+"_Name",entity.Category_Name),
                new SqlParameter(tableName+"_Note",entity.Category_Note)
            };

            string sql = string.Format("UPDATE {0} SET {0}_Name=@{0}_Name,{0}_Note=@{0}_Note WHERE {0}_ID = @{0}_ID", tableName);

            return Convert.ToInt32(db.ExecuteNonQuery(sql, paramArray)) > 0;
        }

        public bool Exists(Category entity)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter(tableName+"_Name",entity.Category_Name),
                new SqlParameter(tableName+"_ID",entity.Category_ID)
            };

            string sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {0}_Name = @{0}_Name AND {0}_ID <> @{0}_ID", tableName);


            return Convert.ToInt32(db.ExecuteScalar(sql, paramArray)) > 0;
        }

        public bool Delete(int Category_ID)
        {
            SqlParameter[] paramArray = new SqlParameter[] 
            { 
                new SqlParameter(tableName+"_ID",Category_ID)
            };

            string sql = string.Format("UPDATE {0} SET {0}_Activated = 0 WHERE {0}_ID=@{0}_ID", tableName);


            return Convert.ToInt32(db.ExecuteNonQuery(sql, paramArray)) > 0;
        }
    }
}
